﻿#include "mainwindow.h"
#include "ui_mainwindow.h"

/***
* https://www.cnblogs.com/wangjian8888/p/9176662.html
*/

MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);

    // 数据格式： 第一个为头数据，为一个Qlist
    //           第二个为内容数据： 最后一位为字符串： 0或者1

    QString fileName = "LogA-10-32-53(1).xlsx";
    QList<QList<QString> > listGlobalData;

    //    setGlobalTableData(listGlobalData);

   /* dataSample();
    exportExcel(fileName);

    //        getExcelItemData(fileName, "A5");
    //    getExcelItemData(fileName, 2, 2);
    //    getExcelAllInfos(fileName);


    QXlsx::Document doc("book1.xlsx");
    qDebug() << "[" << __FUNCTION__ <<__LINE__ << "] :" << doc.read("A1")  << doc.sheetNames();

*/

   ExcelInfos excelInfos = readExcel(fileName);
   for(int i = 0; i < excelInfos.rowCount; ++i)
   {
        for(int j = 0; j < excelInfos.colCount; ++j)
        {
            qDebug() << "[" << __FUNCTION__ <<__LINE__ << "] :" <<  excelInfos.dataTable.at(i).at(j);
        }
   }







}

MainWindow::~MainWindow()
{
    delete ui;
}

QXlsx::Format MainWindow::setFormatStyleBlue()
{
    QXlsx::Format format;/*设置该单元的样式*/
    format.setBorderColor(QColor("#000000"));
    format.setBorderStyle(QXlsx::Format::BorderThin);/*边框样式*/

    format.setFontColor(QColor("#FFFFFF"));/*文字为红色*/
    format.setFontSize(11);/*设置字体大小*/
    format.setFontBold(false);
    format.setFontName(QStringLiteral("Calibri"));

    format.setPatternBackgroundColor(QColor("#00B050"));/*背景颜色*/
    format.setPatternForegroundColor(Qt::red);

    format.setVerticalAlignment(QXlsx::Format::AlignVCenter);
    format.setHorizontalAlignment(QXlsx::Format::AlignHCenter);/*横向居中*/

    return format;
}

QXlsx::Format MainWindow::setFormatStyleRed()
{
    QXlsx::Format format;/*设置该单元的样式*/
    format.setBorderColor(QColor("#000000"));
    format.setBorderStyle(QXlsx::Format::BorderThin);/*边框样式*/

    format.setFontColor(QColor("#FFFFFF"));/*文字为红色*/
    format.setFontSize(11);/*设置字体大小*/
    format.setFontBold(false);
    format.setFontName(QStringLiteral("Calibri"));

    format.setPatternBackgroundColor(QColor("#FF0000"));/*背景颜色*/
    format.setPatternForegroundColor(Qt::red);

    format.setVerticalAlignment(QXlsx::Format::AlignVCenter);
    format.setHorizontalAlignment(QXlsx::Format::AlignHCenter);/*横向居中*/

    return format;
}

QXlsx::Format MainWindow::setHeaderFormatStyle()
{
    QXlsx::Format format;/*设置该单元的样式*/
    format.setBorderColor(QColor("#000000"));
    format.setBorderStyle(QXlsx::Format::BorderThin);/*边框样式*/

    format.setFontColor(QColor("#000000"));/*文字为红色*/
    format.setFontSize(12);/*设置字体大小*/
    format.setFontBold(true);
    format.setFontName(QStringLiteral("Calibri"));

    format.setPatternBackgroundColor(QColor("#A6A6A6"));/*背景颜色*/
    format.setPatternForegroundColor(Qt::red);

    format.setVerticalAlignment(QXlsx::Format::AlignVCenter);
    format.setHorizontalAlignment(QXlsx::Format::AlignHCenter);/*横向居中*/

    return format;
}

QXlsx::Format MainWindow::setSingleFormatStyle()
{
    QXlsx::Format format;/*设置该单元的样式*/
    format.setBorderColor(QColor("#000000"));
    format.setBorderStyle(QXlsx::Format::BorderThin);/*边框样式*/

    format.setFontColor(QColor("#000000"));/*文字为红色*/
    format.setFontSize(11);/*设置字体大小*/
    format.setFontBold(false);
    format.setFontName(QStringLiteral("Calibri"));


    format.setPatternBackgroundColor(QColor("#FFFFFF"));/*背景颜色*/
    format.setPatternForegroundColor(Qt::red);

    format.setVerticalAlignment(QXlsx::Format::AlignVCenter);
    format.setHorizontalAlignment(QXlsx::Format::AlignHCenter);/*横向居中*/

    return format;
}

QXlsx::Format MainWindow::setDoubleFormatStyle()
{
    QXlsx::Format format;/*设置该单元的样式*/
    format.setBorderColor(QColor("#000000"));
    format.setBorderStyle(QXlsx::Format::BorderThin);/*边框样式*/

    format.setFontColor(QColor("#000000"));/*文字为红色*/
    format.setFontSize(11);/*设置字体大小*/
    format.setFontBold(false);
    format.setFontName(QStringLiteral("Calibri"));

    format.setPatternBackgroundColor(QColor("#F2F2F2"));/*背景颜色*/
    format.setPatternForegroundColor(Qt::red);

    format.setVerticalAlignment(QXlsx::Format::AlignVCenter);
    format.setHorizontalAlignment(QXlsx::Format::AlignHCenter);/*横向居中*/

    return format;
}

void MainWindow::dataSample()
{
    QList<QString> header ;
    header << "Sensor" << "MUX" << "Sample"
           << "AD" << "IOC" << "CPU" << "Result";
    m_listGlobalData.append(header);

    for(int row = 0; row < 10; ++row)
    {
        QList<QString> rowData;
        QString  mm = "";
        if(row%2 == 0)
        {
            mm = "1";
        }
        else
        {
            mm = "0";
        }
        rowData << "2" << "5" << "3" << "6" << "5" << "6" << mm;
        m_listGlobalData.append(rowData);
    }
}

void MainWindow::setGlobalTableData(QList<QList<QString> > listGlobalData)
{
    m_listGlobalData = listGlobalData;
}

void MainWindow::exportExcel(QString fileName)
{
    //    QFile file(fileName);
    //    file.open(QFile::WriteOnly|QFile::Truncate);
    //    file.close();

    QXlsx::Document xlsx(fileName);/*打开一个book1的文件*/
    QXlsx::Format formatHeader, formatBlue,
            formatRed, formatSingal, formatDouble;/*设置该单元的样式*/
    formatHeader = setHeaderFormatStyle();
    formatBlue = setFormatStyleBlue();
    formatRed = setFormatStyleRed();
    formatSingal = setSingleFormatStyle();
    formatDouble = setDoubleFormatStyle();
    if(m_listGlobalData.length() > 0)
    {
        for(int row = 0; row < m_listGlobalData.length(); ++row)
        {
            int rowIndex = row + 1;
            QList<QString> rowList = m_listGlobalData.at(row);

            // 表头
            if(row == 0)
            {
                for(int j = 0; j < rowList.length(); j++)
                {

                    xlsx.write(rowIndex, j + 1, rowList.at(j), formatHeader);
                }
            }
            else
            {
                // 表主体
                for(int col = 0; col < rowList.length(); ++col)
                {
                    // 最后一列
                    if(col == rowList.length() - 1)
                    {
                        if(rowList.at(col).toInt() == 0)
                        {
                            xlsx.write(rowIndex, col + 1, rowList.at(col).toInt(), formatRed);
                        }
                        else
                        {
                            xlsx.write(rowIndex, col + 1, rowList.at(col).toInt(), formatBlue);
                        }

                    }
                    else
                    {
                        if(rowIndex%2 == 0)
                        {
                            xlsx.write(rowIndex, col+1, rowList.at(col).toInt(), formatDouble);
                        }
                        else
                        {
                            xlsx.write(rowIndex, col+1, rowList.at(col).toInt(), formatSingal);
                        }
                    }
                }
            }
        }

    }

    xlsx.saveAs(fileName);/*保存*/
}

QString MainWindow::getExcelItemData(QString fileName, QString itemName)
{
    QXlsx::Document xlsx(fileName);/*打开一个book1的文件*/
    QString itemData = xlsx.read(itemName).toString();
    qDebug() << "[" << __FUNCTION__ <<__LINE__ << "] :" <<  itemData;
    return itemData;


}

QString MainWindow::getExcelItemData(QString fileName, int row, int col)
{
    QString itemData ;
    if(row < 1 || col < 1)
    {
        return "-1";
    }
    QXlsx::Document  xlsx(fileName);/*打开一个book1的文件*/
    QStringList sheetList = xlsx.sheetNames();
    if (sheetList.size() > 0)
    {
        itemData = xlsx.read(row, col).toString();
        qDebug() << "[" << __FUNCTION__ <<__LINE__ << "] :" <<  itemData;
    }
    else
    {
        qDebug() << "[" << __FUNCTION__ <<__LINE__ << "] : read error!" ;
        return "-1";
    }

    return itemData;
}

QString MainWindow::getExcelAllInfos(QString fileName)
{
    //载入excel文件
    QXlsx::Document xlsx(fileName);
    QString itemData;
    //获取工作表数目，并且选择第一个
    QStringList sheetList = xlsx.sheetNames();
    if (sheetList.size() > 0) {

        //获取数据表名字
        xlsx.selectSheet(sheetList[0]);
        //获取表格行数和列数
        QXlsx::CellRange range=xlsx.dimension();

        QString xm = "";
        QString sfz = "";
        for (int i = 2; i < range.rowCount()+1; ++i) {
            //读取工作表有用数据插入数据库
            if (xlsx.cellAt(i, 2) != 0) {
                xm = xlsx.cellAt(i, 2)->value().toString().trimmed();
                qDebug() << "[" << __FUNCTION__ <<__LINE__ << "] :" <<  xm;
            }

            if (xlsx.cellAt(i, 3) != 0) {
                sfz= xlsx.cellAt(i, 3)->value().toString().trimmed();
                qDebug() << "[" << __FUNCTION__ <<__LINE__ << "] :" <<  sfz;
            }


            if (!xm.isEmpty() && !sfz.isEmpty()) {
                xm = "";
                sfz = "";
            }
        }

    }
    else {

        return "-1";

    }
    qDebug() << "[" << __FUNCTION__ <<__LINE__ << "] :" <<  itemData;
    return itemData;
}

QString MainWindow::getCntSheetName(QXlsx::Document &doc)
{
    return doc.currentSheet()->sheetName();
}

QStringList MainWindow::getExcelSheetNames(QXlsx::Document &doc)
{
    return doc.sheetNames();
}

ExcelInfos MainWindow::readExcel(QString filename)
{
    ExcelInfos excelInfos;
    QXlsx::Document doc(filename);
    QXlsx::Workbook *workBook = doc.workbook();

    QXlsx::Worksheet *workSheet = static_cast<QXlsx::Worksheet*>(workBook->sheet(0));
    QList<QList<QVariant> > dataTable;
    QList<QVariant> rowData;

    int rowCount = workSheet->dimension().rowCount();
    int colCount = workSheet->dimension().columnCount();

    dataTable.clear();
    for(int row = 1; row <= workSheet->dimension().rowCount(); ++row)
    {
        rowData.clear();
        for(int col = 1; col <= workSheet->dimension().columnCount(); ++col)
        {
            QXlsx::Cell *cell = workSheet->cellAt(row, col);
            rowData.append(cell->value());
        }
        dataTable.append(rowData);
    }


    excelInfos.filename = filename;
    excelInfos.cntSheetName = getCntSheetName(doc);
    excelInfos.sheetnames = getExcelSheetNames(doc);
    excelInfos.rowCount = rowCount;
    excelInfos.colCount = colCount;
    excelInfos.dataTable = dataTable;
    return excelInfos;
}
